-- 语音识别记录表
CREATE TABLE speech_record (
    speech_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT DEFAULT NULL,
    client_user_id BIGINT DEFAULT NULL,
    user_type SMALLINT DEFAULT 1,
    session_id VARCHAR(64) DEFAULT NULL,
    file_name VARCHAR(255) DEFAULT NULL,
    file_path VARCHAR(500) DEFAULT NULL,
    file_size BIGINT DEFAULT NULL,
    duration DECIMAL(10,2) DEFAULT NULL,
    model_name VARCHAR(100) NOT NULL,
    language VARCHAR(10) DEFAULT 'zh',
    recognize_text TEXT,
    confidence DECIMAL(5,4) DEFAULT NULL,
    status SMALLINT DEFAULT 0,
    error_message TEXT,
    recognition_config JSONB,
    recognition_details JSONB,
    api_request_id VARCHAR(64) DEFAULT NULL,
    callback_url VARCHAR(500) DEFAULT NULL,
    callback_status SMALLINT DEFAULT 0,
    create_by VARCHAR(64) DEFAULT '',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) DEFAULT '',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    del_flag SMALLINT DEFAULT 0,
    remark VARCHAR(500) DEFAULT NULL,
    
    -- 确保用户ID二选一
    CONSTRAINT chk_speech_record_user CHECK (
        (user_id IS NOT NULL AND client_user_id IS NULL) OR 
        (user_id IS NULL AND client_user_id IS NOT NULL)
    )
);

-- 添加注释
COMMENT ON TABLE speech_record IS '语音识别记录表';
COMMENT ON COLUMN speech_record.speech_id IS '识别记录ID';
COMMENT ON COLUMN speech_record.user_id IS '系统用户ID（内部用户）';
COMMENT ON COLUMN speech_record.client_user_id IS '客户端用户ID（外部用户）';
COMMENT ON COLUMN speech_record.user_type IS '用户类型：1-系统用户, 2-客户端用户';
COMMENT ON COLUMN speech_record.session_id IS '会话ID';
COMMENT ON COLUMN speech_record.file_name IS '原始文件名';
COMMENT ON COLUMN speech_record.file_path IS '文件存储路径';
COMMENT ON COLUMN speech_record.file_size IS '文件大小(字节)';
COMMENT ON COLUMN speech_record.duration IS '音频时长(秒)';
COMMENT ON COLUMN speech_record.model_name IS '识别模型名称';
COMMENT ON COLUMN speech_record.language IS '语言类型';
COMMENT ON COLUMN speech_record.recognize_text IS '识别结果文本';
COMMENT ON COLUMN speech_record.confidence IS '识别置信度';
COMMENT ON COLUMN speech_record.status IS '状态：0-处理中, 1-成功, 2-失败';
COMMENT ON COLUMN speech_record.error_message IS '错误信息';
COMMENT ON COLUMN speech_record.recognition_config IS '识别配置参数';
COMMENT ON COLUMN speech_record.recognition_details IS '详细识别结果';
COMMENT ON COLUMN speech_record.api_request_id IS 'API请求ID（用于外部客户端）';
COMMENT ON COLUMN speech_record.callback_url IS '回调地址（用于异步通知）';
COMMENT ON COLUMN speech_record.callback_status IS '回调状态：0-未回调, 1-回调成功, 2-回调失败';
COMMENT ON COLUMN speech_record.create_by IS '创建者';
COMMENT ON COLUMN speech_record.create_time IS '创建时间';
COMMENT ON COLUMN speech_record.update_by IS '更新者';
COMMENT ON COLUMN speech_record.update_time IS '更新时间';
COMMENT ON COLUMN speech_record.del_flag IS '删除标志：0-未删除, 1-已删除';
COMMENT ON COLUMN speech_record.remark IS '备注';

-- 创建索引
CREATE INDEX idx_speech_record_user_id ON speech_record (user_id);
CREATE INDEX idx_speech_record_client_user_id ON speech_record (client_user_id);
CREATE INDEX idx_speech_record_user_type ON speech_record (user_type);
CREATE INDEX idx_speech_record_session_id ON speech_record (session_id);
CREATE INDEX idx_speech_record_api_request_id ON speech_record (api_request_id);
CREATE INDEX idx_speech_record_status ON speech_record (status);
CREATE INDEX idx_speech_record_create_time ON speech_record (create_time);
CREATE INDEX idx_speech_record_model_name ON speech_record (model_name);
CREATE INDEX idx_speech_record_del_flag ON speech_record (del_flag);

-- 为JSONB字段创建GIN索引（性能优化）
CREATE INDEX idx_speech_record_recognition_config ON speech_record USING GIN (recognition_config);
CREATE INDEX idx_speech_record_recognition_details ON speech_record USING GIN (recognition_details);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为speech_record表创建更新时间触发器
CREATE TRIGGER update_speech_record_modtime 
    BEFORE UPDATE ON speech_record 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 实时语音识别会话表
CREATE TABLE speech_session (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id BIGINT NOT NULL,
    model_name VARCHAR(100) NOT NULL,
    language VARCHAR(10) DEFAULT 'zh',
    status SMALLINT DEFAULT 0,
    total_duration DECIMAL(10,2) DEFAULT 0,
    total_text_length INTEGER DEFAULT 0,
    segments_count INTEGER DEFAULT 0,
    avg_confidence DECIMAL(5,4) DEFAULT NULL,
    recognition_config JSONB,
    final_result TEXT,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP DEFAULT NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    del_flag SMALLINT DEFAULT 0
);

-- 添加注释
COMMENT ON TABLE speech_session IS '实时语音识别会话表';
COMMENT ON COLUMN speech_session.session_id IS '会话ID';
COMMENT ON COLUMN speech_session.user_id IS '用户ID';
COMMENT ON COLUMN speech_session.model_name IS '识别模型名称';
COMMENT ON COLUMN speech_session.language IS '语言类型';
COMMENT ON COLUMN speech_session.status IS '会话状态：0-活跃, 1-已结束, 2-异常';
COMMENT ON COLUMN speech_session.total_duration IS '总音频时长(秒)';
COMMENT ON COLUMN speech_session.total_text_length IS '总识别文本长度';
COMMENT ON COLUMN speech_session.segments_count IS '音频段数量';
COMMENT ON COLUMN speech_session.avg_confidence IS '平均置信度';
COMMENT ON COLUMN speech_session.recognition_config IS '识别配置参数';
COMMENT ON COLUMN speech_session.final_result IS '最终识别结果';
COMMENT ON COLUMN speech_session.start_time IS '会话开始时间';
COMMENT ON COLUMN speech_session.end_time IS '会话结束时间';
COMMENT ON COLUMN speech_session.last_activity IS '最后活动时间';
COMMENT ON COLUMN speech_session.create_time IS '创建时间';
COMMENT ON COLUMN speech_session.del_flag IS '删除标志：0-未删除, 1-已删除';

-- 创建索引
CREATE INDEX idx_speech_session_user_id ON speech_session (user_id);
CREATE INDEX idx_speech_session_status ON speech_session (status);
CREATE INDEX idx_speech_session_start_time ON speech_session (start_time);
CREATE INDEX idx_speech_session_last_activity ON speech_session (last_activity);
CREATE INDEX idx_speech_session_del_flag ON speech_session (del_flag);

-- 为JSONB字段创建GIN索引（性能优化）
CREATE INDEX idx_speech_session_recognition_config ON speech_session USING GIN (recognition_config);

-- 创建更新last_activity时间的触发器函数
CREATE OR REPLACE FUNCTION update_last_activity_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_activity = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为speech_session表创建更新last_activity的触发器
CREATE TRIGGER update_speech_session_last_activity 
    BEFORE UPDATE ON speech_session 
    FOR EACH ROW EXECUTE FUNCTION update_last_activity_column();

-- 为speech_session表创建更新update_time的触发器
CREATE TRIGGER update_speech_session_modtime 
    BEFORE UPDATE ON speech_session 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 语音识别片段表（用于实时识别的中间结果）
CREATE TABLE speech_segment (
    segment_id BIGSERIAL PRIMARY KEY,
    session_id VARCHAR(64) NOT NULL,
    user_id BIGINT NOT NULL,
    sequence_num INTEGER NOT NULL,
    audio_data BYTEA,
    text_result TEXT,
    confidence DECIMAL(5,4) DEFAULT NULL,
    start_time DECIMAL(10,3) DEFAULT NULL,
    end_time DECIMAL(10,3) DEFAULT NULL,
    is_final SMALLINT DEFAULT 0,
    status SMALLINT DEFAULT 0,
    processing_time DECIMAL(10,3) DEFAULT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加注释
COMMENT ON TABLE speech_segment IS '语音识别片段表';
COMMENT ON COLUMN speech_segment.segment_id IS '片段ID';
COMMENT ON COLUMN speech_segment.session_id IS '会话ID';
COMMENT ON COLUMN speech_segment.user_id IS '用户ID';
COMMENT ON COLUMN speech_segment.sequence_num IS '片段序号';
COMMENT ON COLUMN speech_segment.audio_data IS '音频数据';
COMMENT ON COLUMN speech_segment.text_result IS '识别文本结果';
COMMENT ON COLUMN speech_segment.confidence IS '置信度';
COMMENT ON COLUMN speech_segment.start_time IS '片段开始时间(秒)';
COMMENT ON COLUMN speech_segment.end_time IS '片段结束时间(秒)';
COMMENT ON COLUMN speech_segment.is_final IS '是否为最终结果：0-中间结果, 1-最终结果';
COMMENT ON COLUMN speech_segment.status IS '状态：0-处理中, 1-成功, 2-失败';
COMMENT ON COLUMN speech_segment.processing_time IS '处理耗时(秒)';
COMMENT ON COLUMN speech_segment.create_time IS '创建时间';

-- 创建索引
CREATE INDEX idx_speech_segment_session_id ON speech_segment (session_id);
CREATE INDEX idx_speech_segment_user_id ON speech_segment (user_id);
CREATE INDEX idx_speech_segment_sequence ON speech_segment (session_id, sequence_num);
CREATE INDEX idx_speech_segment_create_time ON speech_segment (create_time);
CREATE INDEX idx_speech_segment_is_final ON speech_segment (is_final);
CREATE INDEX idx_speech_segment_status ON speech_segment (status);
CREATE UNIQUE INDEX uk_speech_segment_session_sequence ON speech_segment (session_id, sequence_num);

-- 语音识别统计表
CREATE TABLE speech_statistics (
    stat_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    stat_date DATE NOT NULL,
    total_recognitions INTEGER DEFAULT 0,
    successful_recognitions INTEGER DEFAULT 0,
    failed_recognitions INTEGER DEFAULT 0,
    total_audio_duration DECIMAL(15,2) DEFAULT 0,
    total_text_length BIGINT DEFAULT 0,
    avg_confidence DECIMAL(5,4) DEFAULT NULL,
    avg_processing_time DECIMAL(10,3) DEFAULT NULL,
    model_usage JSONB,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加注释
COMMENT ON TABLE speech_statistics IS '语音识别统计表';
COMMENT ON COLUMN speech_statistics.stat_id IS '统计ID';
COMMENT ON COLUMN speech_statistics.user_id IS '用户ID';
COMMENT ON COLUMN speech_statistics.stat_date IS '统计日期';
COMMENT ON COLUMN speech_statistics.total_recognitions IS '总识别次数';
COMMENT ON COLUMN speech_statistics.successful_recognitions IS '成功识别次数';
COMMENT ON COLUMN speech_statistics.failed_recognitions IS '失败识别次数';
COMMENT ON COLUMN speech_statistics.total_audio_duration IS '总音频时长(秒)';
COMMENT ON COLUMN speech_statistics.total_text_length IS '总识别文本长度';
COMMENT ON COLUMN speech_statistics.avg_confidence IS '平均置信度';
COMMENT ON COLUMN speech_statistics.avg_processing_time IS '平均处理时间(秒)';
COMMENT ON COLUMN speech_statistics.model_usage IS '模型使用统计';
COMMENT ON COLUMN speech_statistics.create_time IS '创建时间';
COMMENT ON COLUMN speech_statistics.update_time IS '更新时间';

-- 创建索引
CREATE INDEX idx_speech_statistics_user_date ON speech_statistics (user_id, stat_date);
CREATE INDEX idx_speech_statistics_stat_date ON speech_statistics (stat_date);
CREATE UNIQUE INDEX uk_speech_statistics_user_date ON speech_statistics (user_id, stat_date);

-- 为JSONB字段创建GIN索引（性能优化）
CREATE INDEX idx_speech_statistics_model_usage ON speech_statistics USING GIN (model_usage);

-- 为speech_statistics表创建更新时间触发器
CREATE TRIGGER update_speech_statistics_modtime 
    BEFORE UPDATE ON speech_statistics 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 语音模型配置表
CREATE TABLE speech_model_config (
    config_id BIGSERIAL PRIMARY KEY,
    model_name VARCHAR(100) NOT NULL,
    display_name VARCHAR(200) NOT NULL,
    model_type SMALLINT NOT NULL,
    supported_languages JSONB,
    model_path VARCHAR(500),
    model_size BIGINT,
    max_audio_duration INTEGER DEFAULT 300,
    supported_formats JSONB,
    default_config JSONB,
    performance_info JSONB,
    is_enabled SMALLINT DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    create_by VARCHAR(64) DEFAULT '',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64) DEFAULT '',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    remark VARCHAR(500) DEFAULT NULL
);

-- 添加注释
COMMENT ON TABLE speech_model_config IS '语音模型配置表';
COMMENT ON COLUMN speech_model_config.config_id IS '配置ID';
COMMENT ON COLUMN speech_model_config.model_name IS '模型名称';
COMMENT ON COLUMN speech_model_config.display_name IS '显示名称';
COMMENT ON COLUMN speech_model_config.model_type IS '模型类型：1-离线, 2-流式';
COMMENT ON COLUMN speech_model_config.supported_languages IS '支持的语言列表';
COMMENT ON COLUMN speech_model_config.model_path IS '模型文件路径';
COMMENT ON COLUMN speech_model_config.model_size IS '模型大小(字节)';
COMMENT ON COLUMN speech_model_config.max_audio_duration IS '最大音频时长(秒)';
COMMENT ON COLUMN speech_model_config.supported_formats IS '支持的音频格式';
COMMENT ON COLUMN speech_model_config.default_config IS '默认配置参数';
COMMENT ON COLUMN speech_model_config.performance_info IS '性能信息';
COMMENT ON COLUMN speech_model_config.is_enabled IS '是否启用：0-禁用, 1-启用';
COMMENT ON COLUMN speech_model_config.sort_order IS '排序顺序';
COMMENT ON COLUMN speech_model_config.create_by IS '创建者';
COMMENT ON COLUMN speech_model_config.create_time IS '创建时间';
COMMENT ON COLUMN speech_model_config.update_by IS '更新者';
COMMENT ON COLUMN speech_model_config.update_time IS '更新时间';
COMMENT ON COLUMN speech_model_config.remark IS '备注';

-- 创建索引
CREATE INDEX idx_speech_model_config_model_name ON speech_model_config (model_name);
CREATE INDEX idx_speech_model_config_model_type ON speech_model_config (model_type);
CREATE INDEX idx_speech_model_config_is_enabled ON speech_model_config (is_enabled);
CREATE INDEX idx_speech_model_config_sort_order ON speech_model_config (sort_order);
CREATE UNIQUE INDEX uk_speech_model_config_model_name ON speech_model_config (model_name);

-- 为JSONB字段创建GIN索引（性能优化）
CREATE INDEX idx_speech_model_config_supported_languages ON speech_model_config USING GIN (supported_languages);
CREATE INDEX idx_speech_model_config_supported_formats ON speech_model_config USING GIN (supported_formats);
CREATE INDEX idx_speech_model_config_default_config ON speech_model_config USING GIN (default_config);
CREATE INDEX idx_speech_model_config_performance_info ON speech_model_config USING GIN (performance_info);

-- 为speech_model_config表创建更新时间触发器
CREATE TRIGGER update_speech_model_config_modtime 
    BEFORE UPDATE ON speech_model_config 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 插入默认模型配置
INSERT INTO speech_model_config (
    model_name, display_name, model_type, supported_languages, 
    max_audio_duration, supported_formats, default_config, 
    is_enabled, sort_order, remark
) VALUES 
(
    'sensevoice-small', 
    'SenseVoice小型模型', 
    1, 
    '["zh", "en", "yue", "ja", "ko"]'::jsonb,
    600,
    '["wav", "mp3", "flac", "m4a"]'::jsonb,
    '{"use_vad": true, "use_punc": true, "use_itn": true}'::jsonb,
    1, 
    1, 
    '多语言语音识别模型，支持中英文等多种语言'
),
(
    'paraformer-zh', 
    'Paraformer中文模型', 
    1, 
    '["zh"]'::jsonb,
    300,
    '["wav", "mp3", "flac"]'::jsonb,
    '{"use_vad": true, "use_punc": true, "use_itn": true}'::jsonb,
    1, 
    2, 
    '中文语音识别模型，专注中文识别效果'
),
(
    'paraformer-zh-streaming', 
    'Paraformer中文流式模型', 
    2, 
    '["zh"]'::jsonb,
    0,
    '["wav", "mp3", "flac"]'::jsonb,
    '{"use_vad": true, "use_punc": true, "use_itn": true, "chunk_size": 960}'::jsonb,
    1, 
    3, 
    '中文流式语音识别模型，支持实时识别'
);

-- 添加外键约束（现在sys_user表已存在，可以启用外键约束）
-- 系统用户外键约束（可选）
ALTER TABLE speech_record ADD CONSTRAINT fk_speech_record_user 
    FOREIGN KEY (user_id) REFERENCES sys_user(user_id) ON DELETE RESTRICT;

-- 客户端用户外键约束
ALTER TABLE speech_record ADD CONSTRAINT fk_speech_record_client_user 
    FOREIGN KEY (client_user_id) REFERENCES speech_client_user(client_user_id) ON DELETE RESTRICT;

ALTER TABLE speech_session ADD CONSTRAINT fk_speech_session_user 
    FOREIGN KEY (user_id) REFERENCES sys_user(user_id) ON DELETE RESTRICT;

ALTER TABLE speech_segment ADD CONSTRAINT fk_speech_segment_session 
    FOREIGN KEY (session_id) REFERENCES speech_session(session_id) ON DELETE CASCADE;

ALTER TABLE speech_segment ADD CONSTRAINT fk_speech_segment_user 
    FOREIGN KEY (user_id) REFERENCES sys_user(user_id) ON DELETE RESTRICT;

ALTER TABLE speech_statistics ADD CONSTRAINT fk_speech_statistics_user 
    FOREIGN KEY (user_id) REFERENCES sys_user(user_id) ON DELETE RESTRICT;

-- 客户端用户表（语音转写服务用户）
CREATE TABLE speech_client_user (
    client_user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    display_name VARCHAR(100) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    api_key VARCHAR(128) UNIQUE NOT NULL,
    api_secret VARCHAR(128) NOT NULL,
    client_type SMALLINT DEFAULT 1,
    plan_type VARCHAR(20) DEFAULT 'free',
    quota_monthly INTEGER DEFAULT 100,
    quota_used_monthly INTEGER DEFAULT 0,
    quota_reset_date DATE DEFAULT CURRENT_DATE + INTERVAL '1 month',
    rate_limit_per_minute INTEGER DEFAULT 10,
    allowed_models JSONB DEFAULT '["sensevoice-small"]'::jsonb,
    allowed_features JSONB DEFAULT '{"upload": true, "realtime": false, "batch": false}'::jsonb,
    ip_whitelist JSONB DEFAULT NULL,
    webhook_url VARCHAR(500) DEFAULT NULL,
    webhook_secret VARCHAR(128) DEFAULT NULL,
    organization VARCHAR(100) DEFAULT NULL,
    contact_person VARCHAR(50) DEFAULT NULL,
    business_type VARCHAR(50) DEFAULT NULL,
    status SMALLINT DEFAULT 1,
    is_verified SMALLINT DEFAULT 0,
    verification_token VARCHAR(128) DEFAULT NULL,
    last_login_time TIMESTAMP DEFAULT NULL,
    last_login_ip VARCHAR(45) DEFAULT NULL,
    password_hash VARCHAR(255) DEFAULT NULL,
    password_reset_token VARCHAR(128) DEFAULT NULL,
    password_reset_expires TIMESTAMP DEFAULT NULL,
    created_by BIGINT DEFAULT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by BIGINT DEFAULT NULL,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    del_flag SMALLINT DEFAULT 0,
    remark VARCHAR(500) DEFAULT NULL
);

-- 添加注释
COMMENT ON TABLE speech_client_user IS '语音转写客户端用户表';
COMMENT ON COLUMN speech_client_user.client_user_id IS '客户端用户ID';
COMMENT ON COLUMN speech_client_user.username IS '用户名';
COMMENT ON COLUMN speech_client_user.display_name IS '显示名称';
COMMENT ON COLUMN speech_client_user.email IS '邮箱地址';
COMMENT ON COLUMN speech_client_user.phone IS '手机号码';
COMMENT ON COLUMN speech_client_user.avatar IS '头像URL';
COMMENT ON COLUMN speech_client_user.api_key IS 'API密钥';
COMMENT ON COLUMN speech_client_user.api_secret IS 'API密钥密码';
COMMENT ON COLUMN speech_client_user.client_type IS '客户端类型：1-个人, 2-企业, 3-开发者';
COMMENT ON COLUMN speech_client_user.plan_type IS '计划类型：free-免费版, basic-基础版, pro-专业版, enterprise-企业版';
COMMENT ON COLUMN speech_client_user.quota_monthly IS '月度配额（分钟数）';
COMMENT ON COLUMN speech_client_user.quota_used_monthly IS '月度已使用配额';
COMMENT ON COLUMN speech_client_user.quota_reset_date IS '配额重置日期';
COMMENT ON COLUMN speech_client_user.rate_limit_per_minute IS '每分钟请求限制';
COMMENT ON COLUMN speech_client_user.allowed_models IS '允许使用的模型列表';
COMMENT ON COLUMN speech_client_user.allowed_features IS '允许使用的功能';
COMMENT ON COLUMN speech_client_user.ip_whitelist IS 'IP白名单';
COMMENT ON COLUMN speech_client_user.webhook_url IS 'Webhook回调地址';
COMMENT ON COLUMN speech_client_user.webhook_secret IS 'Webhook密钥';
COMMENT ON COLUMN speech_client_user.organization IS '组织机构';
COMMENT ON COLUMN speech_client_user.contact_person IS '联系人';
COMMENT ON COLUMN speech_client_user.business_type IS '业务类型';
COMMENT ON COLUMN speech_client_user.status IS '状态：0-禁用, 1-启用, 2-冻结';
COMMENT ON COLUMN speech_client_user.is_verified IS '是否已验证：0-未验证, 1-已验证';
COMMENT ON COLUMN speech_client_user.verification_token IS '验证令牌';
COMMENT ON COLUMN speech_client_user.last_login_time IS '最后登录时间';
COMMENT ON COLUMN speech_client_user.last_login_ip IS '最后登录IP';
COMMENT ON COLUMN speech_client_user.password_hash IS '密码哈希（可选）';
COMMENT ON COLUMN speech_client_user.password_reset_token IS '密码重置令牌';
COMMENT ON COLUMN speech_client_user.password_reset_expires IS '密码重置令牌过期时间';
COMMENT ON COLUMN speech_client_user.created_by IS '创建者（系统管理员ID）';
COMMENT ON COLUMN speech_client_user.create_time IS '创建时间';
COMMENT ON COLUMN speech_client_user.update_by IS '更新者ID';
COMMENT ON COLUMN speech_client_user.update_time IS '更新时间';
COMMENT ON COLUMN speech_client_user.del_flag IS '删除标志：0-未删除, 1-已删除';
COMMENT ON COLUMN speech_client_user.remark IS '备注';

-- 创建索引
CREATE INDEX idx_speech_client_user_username ON speech_client_user (username);
CREATE INDEX idx_speech_client_user_email ON speech_client_user (email);
CREATE INDEX idx_speech_client_user_api_key ON speech_client_user (api_key);
CREATE INDEX idx_speech_client_user_status ON speech_client_user (status);
CREATE INDEX idx_speech_client_user_client_type ON speech_client_user (client_type);
CREATE INDEX idx_speech_client_user_plan_type ON speech_client_user (plan_type);
CREATE INDEX idx_speech_client_user_create_time ON speech_client_user (create_time);
CREATE INDEX idx_speech_client_user_del_flag ON speech_client_user (del_flag);

-- 为JSONB字段创建GIN索引
CREATE INDEX idx_speech_client_user_allowed_models ON speech_client_user USING GIN (allowed_models);
CREATE INDEX idx_speech_client_user_allowed_features ON speech_client_user USING GIN (allowed_features);
CREATE INDEX idx_speech_client_user_ip_whitelist ON speech_client_user USING GIN (ip_whitelist);

-- 唯一约束
CREATE UNIQUE INDEX uk_speech_client_user_username ON speech_client_user (username) WHERE del_flag = 0;
CREATE UNIQUE INDEX uk_speech_client_user_email ON speech_client_user (email) WHERE del_flag = 0;

-- 创建更新时间触发器
CREATE TRIGGER update_speech_client_user_modtime 
    BEFORE UPDATE ON speech_client_user 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 客户端用户使用统计表
CREATE TABLE speech_client_usage (
    usage_id BIGSERIAL PRIMARY KEY,
    client_user_id BIGINT NOT NULL,
    usage_date DATE NOT NULL,
    total_requests INTEGER DEFAULT 0,
    total_duration DECIMAL(15,2) DEFAULT 0,
    total_characters BIGINT DEFAULT 0,
    model_usage JSONB DEFAULT '{}',
    api_calls INTEGER DEFAULT 0,
    success_rate DECIMAL(5,2) DEFAULT 0,
    avg_response_time DECIMAL(10,3) DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    peak_concurrent INTEGER DEFAULT 0,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加注释
COMMENT ON TABLE speech_client_usage IS '客户端用户使用统计表';
COMMENT ON COLUMN speech_client_usage.usage_id IS '使用统计ID';
COMMENT ON COLUMN speech_client_usage.client_user_id IS '客户端用户ID';
COMMENT ON COLUMN speech_client_usage.usage_date IS '统计日期';
COMMENT ON COLUMN speech_client_usage.total_requests IS '总请求数';
COMMENT ON COLUMN speech_client_usage.total_duration IS '总音频时长（秒）';
COMMENT ON COLUMN speech_client_usage.total_characters IS '总识别字符数';
COMMENT ON COLUMN speech_client_usage.model_usage IS '模型使用统计';
COMMENT ON COLUMN speech_client_usage.api_calls IS 'API调用次数';
COMMENT ON COLUMN speech_client_usage.success_rate IS '成功率（%）';
COMMENT ON COLUMN speech_client_usage.avg_response_time IS '平均响应时间（秒）';
COMMENT ON COLUMN speech_client_usage.error_count IS '错误次数';
COMMENT ON COLUMN speech_client_usage.peak_concurrent IS '峰值并发数';
COMMENT ON COLUMN speech_client_usage.create_time IS '创建时间';
COMMENT ON COLUMN speech_client_usage.update_time IS '更新时间';

-- 创建索引
CREATE INDEX idx_speech_client_usage_client_user_id ON speech_client_usage (client_user_id);
CREATE INDEX idx_speech_client_usage_usage_date ON speech_client_usage (usage_date);
CREATE UNIQUE INDEX uk_speech_client_usage_client_date ON speech_client_usage (client_user_id, usage_date);

-- 为JSONB字段创建GIN索引
CREATE INDEX idx_speech_client_usage_model_usage ON speech_client_usage USING GIN (model_usage);

-- 创建更新时间触发器
CREATE TRIGGER update_speech_client_usage_modtime 
    BEFORE UPDATE ON speech_client_usage 
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- 插入示例客户端用户数据
INSERT INTO speech_client_user (
    username, display_name, email, api_key, api_secret,
    client_type, plan_type, quota_monthly, quota_used_monthly,
    allowed_models, allowed_features, status, created_by, remark
) VALUES 
(
    'demo_client', 
    '演示客户端', 
    'demo@example.com', 
    'sk_' || encode(gen_random_bytes(16), 'hex'),
    encode(gen_random_bytes(32), 'hex'),
    1, 
    'free', 
    100, 
    0,
    '["sensevoice-small"]'::jsonb,
    '{"upload": true, "realtime": false, "batch": false}'::jsonb,
    1, 
    1, 
    '演示用客户端账户'
),
(
    'enterprise_client', 
    '企业客户端', 
    'enterprise@company.com', 
    'sk_' || encode(gen_random_bytes(16), 'hex'),
    encode(gen_random_bytes(32), 'hex'),
    2, 
    'enterprise', 
    10000, 
    0,
    '["sensevoice-small", "paraformer-zh", "paraformer-zh-streaming"]'::jsonb,
    '{"upload": true, "realtime": true, "batch": true}'::jsonb,
    1, 
    1, 
    '企业版客户端账户'
);

-- 外键约束
ALTER TABLE speech_client_usage ADD CONSTRAINT fk_speech_client_usage_client_user 
    FOREIGN KEY (client_user_id) REFERENCES speech_client_user(client_user_id) ON DELETE CASCADE;

-- 可选：为客户端用户创建与系统管理员的关联
ALTER TABLE speech_client_user ADD CONSTRAINT fk_speech_client_user_created_by 
    FOREIGN KEY (created_by) REFERENCES sys_user(user_id) ON DELETE SET NULL;

ALTER TABLE speech_client_user ADD CONSTRAINT fk_speech_client_user_update_by 
    FOREIGN KEY (update_by) REFERENCES sys_user(user_id) ON DELETE SET NULL; 